/**
 * 
 */
package id.co.smltech.servo.project.ejb.dao;

import id.co.smltech.servo.project.utils.SystemConfiguration;

import java.io.File;
import java.io.FileInputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Pattern;

import javax.ejb.EJB;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import model.ActivityTracking;

/**
 * @author Sml
 *
 */

@Stateless
public class ActivityTrackingDao {

	@PersistenceContext(unitName="NP_JPA") EntityManager em; 
	@EJB SystemConfiguration systemConfiguration;
	
	public ActivityTrackingDao() {
		// TODO Auto-generated constructor stub
	}
	
	public void addActivityTracking(ActivityTracking activityTracking)
	{
		em.persist(activityTracking);
	}
	
	public void updateActivityTracking(ActivityTracking activityTracking)
	{	
		em.merge(activityTracking);
	}
	
	public void deleteActivityTracking(ActivityTracking activityTracking)
	{	
		em.remove(activityTracking);
	}
	
	public void addActivityTrackings(List<ActivityTracking> activityTrackings)
	{
		for(ActivityTracking activityTracking : activityTrackings)
		{
			em.persist(activityTracking);
		}
	}
			
	public ActivityTracking getActivityTrackingById(int idAt)
	{
		ActivityTracking activityTracking = em.find(ActivityTracking.class, idAt);
		return activityTracking;
	}
	
	public List<ActivityTracking> getActivityTracking() {
		String jpql = "SELECT at FROM ActivityTracking at ORDER BY ds.idAt ASC";
		Query query = em.createQuery(jpql);
		return query.getResultList();
	}
	
	public void uploadFileExcelActivityTracking(){
		systemConfiguration.init();
		this.parserFileExcelToObject(systemConfiguration.getDirUploadActivityTracking());
	}
	
	private boolean isExcelFile(String fileName) {
		boolean b = false;
		
		if (fileName.endsWith("xls") || fileName.endsWith("xlsx")) {
			b = true;
		}
		
		return b;
	}
	
	public List<List<Object>> parserFileExcelToObject(String dirParam) throws IllegalArgumentException {
		
		int fileDeleted = 0;
		String fileName = "";
		
		List<List<Object>> returnList = new ArrayList<>();
		
		try{
			File directory = new File(dirParam);
			System.out.println("Dir : "+directory);
			File[] fList = directory.listFiles();
			
			for(File file : fList){
				
				if(file.isFile()){
					
					fileName = file.getName();
//						System.out.println("Any File : "+fileName);
						
					if(Pattern.compile(".*_upload-file-excel-sims.*").matcher(fileName).matches()){
						if (!isExcelFile(fileName)) {
							throw new IllegalArgumentException("The file is not an excel file.");
						}
						
						System.out.println("Target File : "+fileName);
						
						FileInputStream fis = new FileInputStream(dirParam+fileName);
						//Create Workbook instance for xlsx/xls file input stream
			            Workbook workbook = new HSSFWorkbook(fis);
			            if(fileName.toLowerCase().endsWith("xlsx")){
			                workbook = new XSSFWorkbook(fis);
			            }
			            
			            //Get the number of sheets in the xlsx file
			            int numberOfSheet = workbook.getNumberOfSheets();
			            
			            for(int i=0; i<numberOfSheet; i++){
			            	
			            	//Get the nth sheet from the workbook
							Sheet sheet = workbook.getSheetAt(i);
							
							String sheetName = workbook.getSheetName(i);
							System.out.println("Sheet name : "+sheetName);
																						
								int rowStart = 1,
									rowEnd = sheet.getLastRowNum()+1,
									cellStart = 0,
									cellEnd = 17;
								
								System.out.println("Row count : "+rowEnd+"\n"+
									"Cell count : "+cellEnd);
								
								String data = "";
								String[] dataArr = new String[cellEnd];
								
								String[][] dataRowPair = new String[0][0];
								int[] equipAntId = new int[0];
								

								for(int j=rowStart; j<rowEnd; j++){
									
									//Get the nth row from the sheet
									Row row = sheet.getRow(j);
									
									for(int k=cellStart; k<cellEnd; k++){
										
										//Get the nth cell/column from the sheet
										Cell cell = row.getCell(k);
										
										//Set cell type value
										data = this.setCellTypeValue(cell, data);
										
										//Clean data value
										data = data.trim().replaceAll("\n", "").replaceAll("\r", "");
										
										//remove .0 (dot point zero) char that's generated by excel default value
										if(data.length() >= 3){
											if(data.charAt(data.length()-2) == '.' && data.charAt(data.length()-1) == '0') //-2 dan -1 utk dapatin .(point) dan 0(nol)
												data = data.substring(0,data.length()-2);
										}
										
										dataArr[k] = data;
										
										//Reset value
										data = "";
									}
									
									//check before insert
									int checkEmptyValue = 0;
									for(int k=cellStart; k<cellEnd; k++){
										if(dataArr[k].equalsIgnoreCase(""))
											checkEmptyValue++;
									}
									
									int emptyRecord = 0;
									if(checkEmptyValue == cellEnd-cellStart)
										emptyRecord = 1;
									
									//insert
									if(emptyRecord == 0){
										
//										for(int k=cellStart; k<cellEnd; k++){
//											System.out.print("data["+j+"]["+k+"] : |"+dataArr[k]+"|<>");
//										}
//										System.out.println("");
										
										List<Object> rowList = new ArrayList<>();
										
										for(int k=cellStart; k<cellEnd; k++){
//											System.out.println(dataArr[k]);
											rowList.add(dataArr[k]);
										}
										
										returnList.add(rowList);
											
										System.out.println("===================================");
									}
									
									checkEmptyValue = 0;
									emptyRecord = 0;
									
								}
								
							
							
			            }
			            fis.close();
			            
			            //delete file
						this.deleteFile(dirParam, file.getName());
						fileDeleted = 1;
					}
				}
			}			
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally{
			if(fileDeleted == 0)
				this.deleteFile(dirParam, fileName);
		}
		
		return returnList;
	}
	
	
	//delete file
	public String deleteFile(String source, String file){
		try{
			Files.delete(Paths.get(source+file));
			System.out.println("File "+source+file+" has been deleted");	
		}
		catch(Exception e){
			e.printStackTrace();
		}
		
		return "";
	}
	
	String setCellTypeValue(Cell cell, String data){
		try{
			
			if(cell != null){
				
				//Set cell type value
				if(cell.getCellType() == Cell.CELL_TYPE_STRING){
//					System.out.println("TYPE STRING");
					data = String.valueOf(cell.getStringCellValue());
				}
				else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
					
//					System.out.println("TYPE NUMERIC");
					if(DateUtil.isCellDateFormatted(cell)){
//						System.out.println("TYPE DATE");
						Date date = cell.getDateCellValue();
						DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
						data = dateFormat.format(date);
					}
					else{
//						System.out.println("TYPE NUMERIC NOT DATE");
						data = String.valueOf(cell.getNumericCellValue());
						
						//fungsi if di bawah : kalau ketemu semisal "2.5E7" (digit yg terkonvert jadi E), kita convert ke digit seluruhnya
						if(Pattern.compile(".*E.*").matcher(data).matches()){
							long number = Double.valueOf(data).longValue();
							data = String.valueOf(number);
						}
					}
				}
				else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){
					
//					System.out.println("TYPE FORMULA");
					if(cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC){
						
						if(DateUtil.isCellDateFormatted(cell)){
//							System.out.println("TYPE DATE");
							Date date = cell.getDateCellValue();
							DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
							data = dateFormat.format(date);
						}
						else{
//							System.out.println("TYPE FORMULA NUMERIC");
							data = String.valueOf(cell.getNumericCellValue());
							
							//fungsi if di bawah : kalau ketemu semisal "2.5E7" (digit yg terkonvert jadi E), kita convert ke digit seluruhnya
							if(Pattern.compile(".*E.*").matcher(data).matches()){
								long number = Double.valueOf(data).longValue();
								data = String.valueOf(number);
							}
						}
					}
					else
						data = String.valueOf(cell.getStringCellValue());
				}
			}
			else
				data = "";
		}
		catch(Exception e){
			e.printStackTrace();
		}
		
		return data;
	}
}
